"""
1. 安装依赖
# mysql-connector-python。python 连接 mysql 驱动
# Faker‌是一个Python库，用于生成各种类型的随机数据，如姓名、地址、电话号码、日期、文本等。它非常适合在测试、开发和演示中生成逼真的假数据。Faker支持多种语言和区域设置，可以生成符合特定语言习惯的数据。
#     Faker的主要功能
#         1. ‌生成随机姓名‌：包括全名、名字、姓氏等。
#         2. ‌生成随机地址‌：包括街道地址、城市、国家、邮政编码等。
#         3. ‌生成随机电话号码‌：包括国际格式和本地格式的电话号码。
#         4. ‌生成随机日期和时间‌：包括日期、时间、时间戳等。
#         5. ‌生成随机文本‌：包括句子、段落、文章等。
#         6. ‌生成随机数字和颜色‌：包括整数、浮点数、十六进制颜色代码等。
#         7. ‌支持多语言‌：Faker支持多种语言和区域设置，可以生成符合特定语言习惯的数据‌

pip install mysql-connector-python faker


2. 修改脚本中的数据库连接参数
3. 运行脚本
python mysql_data_generator.py
"""
import random
import time
import uuid
from datetime import datetime, timedelta

import mysql.connector
from faker import Faker

"""
表创建
"""
def create_test_table(connection):
    # 创建测试表
    cursor = connection.cursor()
    createDDL = """
        CREATE TABLE IF NOT EXISTS test_data (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,  -- 主键索引
            uuid CHAR(36) NOT NULL UNIQUE,  -- 普通唯一索引
            user_id INT NOT NULL,
            username VARCHAR(50) NOT NULL,
            email VARCHAR(100) NOT NULL,
            age TINYINT NOT NULL,
            gender ENUM('M', 'F', 'O') NOT NULL,
            is_active TINYINT(1) NOT NULL DEFAULT 1,
            score FLOAT NOT NULL,
            group_id INT NOT NULL,
            category VARCHAR(20) NOT NULL,
            updated_at DATETIME NOT NULL,
            created_at DATETIME NOT NULL,
            INDEX idx_user_id (user_id),  -- 普通索引
            INDEX idx_created_at (created_at),  -- 普通索引
            INDEX idx_username_email (username, email),  -- 联合索引
            INDEX idx_age_gender (age, gender),  -- 联合索引
            INDEX idx_group_score (group_id, score)  -- 联合索引
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    """
    cursor.execute(createDDL)
    connection.commit()
    print("表创建成功")

"""
插入数据
批量插入，一次性1000条。
"""
def insert_data(connection,batch_size = 1000):
    """批量插入数据"""
    fake = Faker()
    total_records = 1000000
    batches = total_records // batch_size

    cursor = connection.cursor()
    insertDML = """
    INSERT INTO test_data 
    (uuid, user_id, username, email, age, gender, created_at, updated_at, is_active, score, group_id, category)
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    """

    start_time = time.time()

    for batch in range(batches):
        data = []
        for _ in range(batch_size):
            base_time = datetime.now() - timedelta(days=random.randint(1, 365))
            created_at = base_time + timedelta(seconds=random.randint(1, 86400))
            updated_at = created_at + timedelta(seconds=random.randint(1, 86400))

            data.append((
                str(uuid.uuid4()),
                random.randint(1, 1000000),  # user_id
                fake.user_name(),
                fake.email(),
                random.randint(18, 80),
                random.choice(['M', 'F', 'O']),
                created_at,
                updated_at,
                random.randint(0, 1),
                round(random.uniform(0, 100), 2),
                random.randint(1, 10),
                random.choice(['A', 'B', 'C', 'D', 'E'])
            ))

        try:
            cursor.executemany(insertDML, data)
            connection.commit()
            if batch % 100 == 0:
                elapsed = time.time() - start_time
                records_inserted = (batch + 1) * batch_size
                print(f"已插入 {records_inserted:,} 条记录，耗时: {elapsed:.2f} 秒，当前时间：{time.time()}")
        except Exception as e:
            print(f"插入数据时出错: {e}")
            connection.rollback()

    total_time = time.time() - start_time
    print(f"插入完成！共插入 {total_records:,} 条记录，总耗时: {total_time:.2f} 秒")
    print(f"平均插入速度: {total_records / total_time:.2f} 条/秒")

if __name__ == '__main__':
    connection = mysql.connector.connect(
         # host='192.168.2.24',
         # port=3306,
        host = 'frp-gap.com',
        port = 12409,
        user='root',
        password='Chimm@1008611',
        database='demo_test',
        autocommit=False
    )
    try:

        if connection.is_connected():
            print("已成功连接到 MySQL 服务器")

            # 1. 创建测试表
            create_test_table(connection)

            # 2. 插入数据
            insert_data(connection)
    except Exception as e:
        print(f"数据库连接错误：{e}")
    finally:
        if connection and connection.is_connected():
            connection.close()
            print("数据库连接已关闭")


